const pool = require('../config/database');

// 创建新订单
const createOrder = async (userId, drugId, quantity) => {
    const connection = await pool.getConnection();
    try {
        await connection.beginTransaction();

        // 获取药品信息
        const [drugRows] = await connection.execute('SELECT * FROM drugs WHERE id = ?', [drugId]);
        if (drugRows.length === 0) {
            throw new Error('Drug not found');
        }
        const drug = drugRows[0];

        if (drug.stock < quantity) {
            throw new Error('Insufficient stock');
        }

        // 计算总价
        const totalPrice = drug.price * quantity;

        // 创建订单
        const [orderResult] = await connection.execute(
            'INSERT INTO orders (user_id, drug_id, quantity, total_price) VALUES (?, ?, ?, ?)',
            [userId, drugId, quantity, totalPrice]
        );

        // 更新药品库存
        await connection.execute(
            'UPDATE drugs SET stock = stock - ? WHERE id = ?',
            [quantity, drugId]
        );

        await connection.commit();
        return orderResult.insertId;
    } catch (error) {
        await connection.rollback();
        throw error;
    } finally {
        connection.release();
    }
};

// 获取用户订单
const getUserOrders = async (userId) => {
    try {
        const [rows] = await pool.execute(
            `SELECT o.id, o.user_id, o.quantity, o.total_price, o.status, o.created_at, o.updated_at,
                    d.id as drug_id, d.name as drug_name, d.price as drug_price, d.image as drug_image, d.category as drug_category,
                    u.username, u.name as user_name
             FROM orders o
             JOIN drugs d ON o.drug_id = d.id
             JOIN users u ON o.user_id = u.id
             WHERE o.user_id = ?
             ORDER BY o.created_at DESC`,
            [userId]
        );

        const orders = rows.map(row => {
            return {
                id: row.id,
                user_id: row.user_id,
                username: row.username,
                totalAmount: row.total_price,
                status: row.status,
                createdAt: row.created_at,
                updatedAt: row.updated_at,
                items: [
                    {
                        id: row.drug_id,
                        name: row.drug_name,
                        quantity: row.quantity,
                        price: row.drug_price,
                        image: row.drug_image,
                        category: row.drug_category
                    }
                ]
            };
        });

        return orders;
    } catch (error) {
        throw error;
    }
};

// 获取所有订单
const getAllOrders = async () => {
    try {
        const [rows] = await pool.execute(
            `SELECT o.id, o.user_id, o.quantity, o.total_price, o.status, o.created_at, o.updated_at,
                    d.id as drug_id, d.name as drug_name, d.price as drug_price, d.image as drug_image, d.category as drug_category,
                    u.username, u.name as user_name
             FROM orders o
             JOIN drugs d ON o.drug_id = d.id
             JOIN users u ON o.user_id = u.id
             ORDER BY o.created_at DESC`
        );

        const orders = rows.map(row => {
            return {
                id: row.id,
                user_id: row.user_id,
                username: row.username,
                totalAmount: row.total_price,
                status: row.status,
                createdAt: row.created_at,
                updatedAt: row.updated_at,
                items: [
                    {
                        id: row.drug_id,
                        name: row.drug_name,
                        quantity: row.quantity,
                        price: row.drug_price,
                        image: row.drug_image,
                        category: row.drug_category
                    }
                ]
            };
        });

        return orders;
    } catch (error) {
        throw error;
    }
};

// 根据 ID 获取订单
const getOrderById = async (id) => {
    try {
        const [rows] = await pool.execute(
            `SELECT o.id, o.user_id, o.quantity, o.total_price, o.status, o.created_at, o.updated_at,
                    d.id as drug_id, d.name as drug_name, d.price as drug_price, d.image as drug_image, d.category as drug_category,
                    u.username, u.name as user_name
             FROM orders o
             JOIN drugs d ON o.drug_id = d.id
             JOIN users u ON o.user_id = u.id
             WHERE o.id = ?`,
            [id]
        );

        if (rows.length === 0) {
            return null;
        }

        const row = rows[0];
        const order = {
            id: row.id,
            user_id: row.user_id,
            username: row.username,
            totalAmount: row.total_price,
            status: row.status,
            createdAt: row.created_at,
            updatedAt: row.updated_at,
            items: [
                {
                    id: row.drug_id,
                    name: row.drug_name,
                    quantity: row.quantity,
                    price: row.drug_price,
                    image: row.drug_image,
                    category: row.drug_category
                }
            ]
        };

        return order;
    } catch (error) {
        throw error;
    }
};

// 更新订单信息
const updateOrderById = async (id, drugId, quantity) => {
    const connection = await pool.getConnection();
    try {
        await connection.beginTransaction();

        // 获取原订单信息
        const [oldOrderRows] = await connection.execute('SELECT * FROM orders WHERE id = ?', [id]);
        if (oldOrderRows.length === 0) {
            throw new Error('Order not found');
        }
        const oldOrder = oldOrderRows[0];

        // 恢复原药品库存
        await connection.execute(
            'UPDATE drugs SET stock = stock + ? WHERE id = ?',
            [oldOrder.quantity, oldOrder.drug_id]
        );

        // 获取新药品信息
        const [drugRows] = await connection.execute('SELECT * FROM drugs WHERE id = ?', [drugId]);
        if (drugRows.length === 0) {
            throw new Error('Drug not found');
        }
        const drug = drugRows[0];

        if (drug.stock < quantity) {
            throw new Error('Insufficient stock');
        }

        // 计算新总价
        const totalPrice = drug.price * quantity;

        // 更新订单信息
        await connection.execute(
            'UPDATE orders SET drug_id = ?, quantity = ?, total_price = ? WHERE id = ?',
            [drugId, quantity, totalPrice, id]
        );

        // 更新新药品库存
        await connection.execute(
            'UPDATE drugs SET stock = stock - ? WHERE id = ?',
            [quantity, drugId]
        );

        await connection.commit();
        return true;
    } catch (error) {
        await connection.rollback();
        throw error;
    } finally {
        connection.release();
    }
};

// 删除订单
const deleteOrderById = async (id) => {
    const connection = await pool.getConnection();
    try {
        await connection.beginTransaction();

        // 获取订单信息
        const [orderRows] = await connection.execute('SELECT * FROM orders WHERE id = ?', [id]);
        if (orderRows.length === 0) {
            throw new Error('Order not found');
        }
        const order = orderRows[0];

        // 恢复药品库存
        await connection.execute(
            'UPDATE drugs SET stock = stock + ? WHERE id = ?',
            [order.quantity, order.drug_id]
        );

        // 删除订单
        await connection.execute('DELETE FROM orders WHERE id = ?', [id]);

        await connection.commit();
        return true;
    } catch (error) {
        await connection.rollback();
        throw error;
    } finally {
        connection.release();
    }
};

// 更新订单状态
const updateOrderStatus = async (id, status) => {
    try {
        const [result] = await pool.execute(
            'UPDATE orders SET status = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?',
            [status, id]
        );
        if (result.affectedRows === 0) {
            throw new Error('订单未找到或状态未改变');
        }
        return true;
    } catch (error) {
        throw error;
    }
};

module.exports = {
    getUserOrders,
    getAllOrders,
    getOrderById,
    createOrder,
    updateOrderById,
    deleteOrderById,
    updateOrderStatus
};